package exercise;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class EX01_SungJukDAO implements EX01_ISungJukDAO {
	
	private final String DRV = "oracle.jdbc.driver.OracleDriver";
	private final String URL = "jdbc:oracle:thin:jwj8508/jwj8508@112.216.253.125:1521:xe";
	private String insertSQL = "insert into ex01sungjuk values (exam.nextval, ?, ?, ?, ?, ?, ?, ?)";	
	private String selectSQL = "select * from ex01sungjuk order by sjno desc";
	private String updateSQL = "update ex01sungjuk set name = ?, kor = ?, eng = ?, mat = ?, tot = ?, avg = ?, grd =? where sjno = ?";
	private String deleteSQL = "delete ex01sungjuk where sjno =?";
	
	@Override
	public boolean insertSungJuk(EX01_SungJukVO s) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try {
			conn = this.openDatabase();
			pstmt = conn.prepareStatement(insertSQL);
			
			pstmt.setString(1, s.getName());
			pstmt.setInt(2, s.getKor());
			pstmt.setInt(3, s.getEng());
			pstmt.setInt(4, s.getMat());
			pstmt.setInt(5, s.getTot());
			pstmt.setFloat(6, s.getAvg());
			pstmt.setString(7, s.getGrd());
				
			pstmt.executeUpdate();
			result = true;
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			this.closeDatabase(conn, pstmt, null);
		}
		
		return result;
	} // insertSungJuk	
	
	@Override
	public List<EX01_SungJukVO> selectSungJuk() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<EX01_SungJukVO> result = new ArrayList<>();
		
		try {
			conn = this.openDatabase();
			pstmt = conn.prepareStatement(selectSQL);
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				EX01_SungJukVO s = new EX01_SungJukVO();
				s.setSjno(rs.getString("sjno"));
				s.setName(rs.getString("name"));
				s.setKor(rs.getInt("kor"));
				s.setEng(rs.getInt("eng"));
				s.setMat(rs.getInt("mat"));
				s.setTot(rs.getInt("tot"));
				s.setAvg(rs.getFloat("avg"));
				s.setGrd(rs.getString("grd"));
				
				result.add(s);
			}
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally{
			this.closeDatabase(conn, pstmt, rs);
		}
		
		
		return result;
	} // selectSungJuk

	private Connection openDatabase() {
		try {
			Class.forName(DRV);
			return DriverManager.getConnection(URL);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	} // openDatabase	

	private void closeDatabase(Connection c, PreparedStatement p, ResultSet r) {
		if (r != null) try{r.close(); r = null;} catch(Exception e){}
		if (p != null) try{p.close(); p = null;} catch(Exception e){}
		if (c != null) try{c.close(); c = null;} catch(Exception e){}
	} // closeDatabase

	@Override
	public boolean updateSungJuk(EX01_SungJukVO s) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try {
			conn = this.openDatabase();
			pstmt = conn.prepareStatement(updateSQL);
			pstmt.setString(1, s.getName());
			pstmt.setInt(2, s.getKor());
			pstmt.setInt(3, s.getEng());
			pstmt.setInt(4, s.getMat());
			pstmt.setInt(5, s.getTot());
			pstmt.setFloat(6, s.getAvg());
			pstmt.setString(7, s.getGrd());
			pstmt.setString(8, s.getSjno());
			
			
			int cnt = pstmt.executeUpdate();
			
			if (cnt == 0) System.out.println("수정된 정보 없음");
			
			result = true;		
			
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			this.closeDatabase(conn, pstmt, null);
		}
		
		return result;
	}

	@Override
	public boolean deleteSungJuk(EX01_SungJukVO s) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		boolean result = false;
		
		try {
			conn = this.openDatabase();
			pstmt = conn.prepareStatement(deleteSQL);
			pstmt.setString(1, s.getSjno());
			
			int cnt = pstmt.executeUpdate();
			
			if (cnt == 0) System.out.println("삭제된 정보 없음");
			
			result = true;
			
		} catch (Exception ex){
			ex.printStackTrace();
		} finally {
			this.closeDatabase(conn, pstmt, null);
		}
		
		return result;
	}

	
	
}
